package org.jetnuke.dao.generic;

import org.apache.log4j.Logger;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import net.jforum.JForumExecutionContext;
import net.jforum.dao.generic.AutoKeys;
import net.jforum.util.preferences.SystemGlobals;

import org.jetnuke.dao.PictureDAO;
import org.jetnuke.entities.Picture;

public class GenericPictureDAO extends AutoKeys implements PictureDAO {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger
			.getLogger(GenericPictureDAO.class);

	public Picture retrieveByPK(int id) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("retrieveByPK(int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(
						SystemGlobals.getSql("PictureModel.selectByID"));
		p.setInt(1, id);

		ResultSet rs = p.executeQuery();
		Picture s = new Picture();

		rs.next();
		fillPictureFromResultSet(s, rs);

		rs.close();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("retrieveByPK(int) - end"); //$NON-NLS-1$
		}
		return s;
	}

	protected void fillPictureFromResultSet(Picture s, ResultSet rs)
			throws Exception {
		if (logger.isDebugEnabled()) {
			logger
					.debug("fillPictureFromResultSet(Picture, ResultSet) - start"); //$NON-NLS-1$
		}

		s.setAID(rs.getInt("AID"));
		s.setAPPROVED(rs.getString("APPROVED"));
		s.setCAPTION(rs.getString("CAPTION"));
		s.setFILENAME(rs.getString("FILENAME"));
		s.setFILEPATH(rs.getString("FILEPATH"));
		s.setFILESIZE(rs.getInt("FILESIZE"));
		s.setHITS(rs.getInt("HITS"));
		s.setKEYWORDS(rs.getString("KEYWORDS"));
		s.setMEDIUMMIMETYPE(rs.getString("MEDIUMMIMETYPE"));
		s.setMEDIUMNAME(rs.getString("MEDIUMNAME"));
		s.setMIMETYPE(rs.getString("MIMETYPE"));
		s.setMTIME(rs.getTimestamp("MTIME"));
		s.setPHEIGHT(rs.getInt("PHEIGHT"));
		s.setPIC_RATING(rs.getInt("PIC_RATING"));
		s.setPIC_RAW_IP(rs.getString("PIC_RAW_IP"));
		s.setPWIDTH(rs.getInt("PWIDTH"));
		s.setTITLE(rs.getString("TITLE"));
		s.setTOTAL_SIZE(rs.getInt("TOTAL_FILESIZE"));
		s.setTHUMBMIMETYPE(rs.getString("THUMBMIMETYPE"));
		s.setTHUMBNAME(rs.getString("THUMBNAME"));
		s.setUID(rs.getInt("UID"));
		s.setVOTES(rs.getInt("VOTES"));
		s.setPID(rs.getInt("PID"));

		if (logger.isDebugEnabled()) {
			logger.debug("fillPictureFromResultSet(Picture, ResultSet) - end"); //$NON-NLS-1$
		}
	}

	public int addNew(Picture picture) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("addNew(Picture) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = this
				.getStatementForAutoKeys("PictureModel.addNew");
		this.setAutoGeneratedKeysQuery(SystemGlobals
				.getSql("PictureModel.lastGeneratedPictureID"));
		initPicture(picture, p);

		int id = this.executeAutoKeysQuery(p);
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("addNew(Picture) - end"); //$NON-NLS-1$
		}
		return id;
	}

	private void initPicture(Picture picture, PreparedStatement p)
			throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("initPicture(Picture, PreparedStatement) - start"); //$NON-NLS-1$
		}

		p.setInt(1, picture.getAID());
		p.setString(2, picture.getAPPROVED());
		p.setString(3, picture.getCAPTION());
		p.setString(4, picture.getFILENAME());
		p.setString(5, picture.getFILEPATH());
		p.setInt(6, picture.getFILESIZE());
		p.setInt(7, picture.getHITS());
		p.setString(8, picture.getKEYWORDS());
		p.setTimestamp(9, new Timestamp(picture.getMTIME().getTime()));
		p.setInt(10, picture.getPHEIGHT());
		p.setInt(11, picture.getPIC_RATING());
		p.setString(12, picture.getPIC_RAW_IP());
		p.setInt(13, picture.getPWIDTH());
		p.setString(14, picture.getTITLE());
		p.setInt(15, picture.getTOTAL_SIZE());
		p.setInt(16, picture.getUID());
		p.setInt(17, picture.getVOTES());
		p.setString(18, picture.getMIMETYPE());
		p.setString(19, picture.getTHUMBNAME());
		p.setString(20, picture.getTHUMBMIMETYPE());
		p.setString(21, picture.getMEDIUMNAME());
		p.setString(22, picture.getMEDIUMMIMETYPE());

		if (logger.isDebugEnabled()) {
			logger.debug("initPicture(Picture, PreparedStatement) - end"); //$NON-NLS-1$
		}
	}

	public List retrieveByUser(int id) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("retrieveByUser(int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(
						SystemGlobals.getSql("PictureModel.selectByUser"));
		p.setInt(1, id);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("retrieveByUser(int) - end"); //$NON-NLS-1$
		}
		return list;
	}

	protected List processSelectAll(ResultSet rs) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("processSelectAll(ResultSet) - start"); //$NON-NLS-1$
		}

		List list = new ArrayList();
		while (rs.next()) {
			Picture u = new Picture();
			fillPictureFromResultSet(u, rs);
			list.add(u);
		}

		if (logger.isDebugEnabled()) {
			logger.debug("processSelectAll(ResultSet) - end"); //$NON-NLS-1$
		}
		return list;
	}

	public void update(Picture picture) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("update(Picture) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(SystemGlobals.getSql("PictureModel.update"));
		p.setInt(1, picture.getAID());
		p.setString(2, picture.getAPPROVED());
		p.setString(3, picture.getCAPTION());
		p.setString(4, picture.getFILENAME());
		p.setString(5, picture.getFILEPATH());
		p.setInt(6, picture.getFILESIZE());
		p.setInt(7, picture.getHITS());
		p.setString(8, picture.getKEYWORDS());
		p.setTimestamp(9, new Timestamp(picture.getMTIME().getTime()));
		p.setInt(10, picture.getPHEIGHT());
		p.setInt(11, picture.getPIC_RATING());
		p.setString(12, picture.getPIC_RAW_IP());
		p.setInt(13, picture.getPWIDTH());
		p.setString(14, picture.getTITLE());
		p.setInt(15, picture.getTOTAL_SIZE());
		p.setInt(16, picture.getUID());
		p.setInt(17, picture.getVOTES());
		p.setString(18, picture.getMIMETYPE());
		p.setString(19, picture.getTHUMBNAME());
		p.setString(20, picture.getTHUMBMIMETYPE());
		p.setString(21, picture.getMEDIUMNAME());
		p.setString(22, picture.getMEDIUMMIMETYPE());
		p.setInt(23, picture.getPID());
		p.executeUpdate();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("update(Picture) - end"); //$NON-NLS-1$
		}
	}

	public List getLast(int last) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("getLast(int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(
						SystemGlobals.getSql("PictureModel.selectLast"));
		p.setInt(1, last);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("getLast(int) - end"); //$NON-NLS-1$
		}
		return list;
	}

	public void moveAlbum(int userid, int init, int final_) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("moveAlbum(int, int, int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(
						SystemGlobals.getSql("PictureModel.moveAlbum"));
		p.setInt(1, final_);
		p.setInt(2, userid);
		p.setInt(3, init);
		p.executeUpdate();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("moveAlbum(int, int, int) - end"); //$NON-NLS-1$
		}
	}

	public List selectByAlbum(int aid) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("selectByAlbum(int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(
						SystemGlobals.getSql("PictureModel.selectByAlbum"));
		p.setInt(1, aid);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("selectByAlbum(int) - end"); //$NON-NLS-1$
		}
		return list;
	}

	public int getLastByAlbum(int aid) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("getLastByAlbum(int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(
						SystemGlobals.getSql("PictureModel.getLastByAlbum"));
		p.setInt(1, aid);
		ResultSet rs = p.executeQuery();
		int ret = 0;
		if (rs.next()) {

			ret = rs.getInt(1);
			if (rs.wasNull())
				ret = 0;
		}
		rs.close();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("getLastByAlbum(int) - end"); //$NON-NLS-1$
		}
		return ret;
	}

	public List search(String searchval, int start, int limit) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("search(String, int, int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(SystemGlobals.getSql("PictureModel.search"));
		String val = "%" + searchval + "%";
		p.setString(1, val);
		p.setString(2, val);
		p.setString(3, val);
		p.setString(4, val);
		p.setInt(5, start);
		p.setInt(6, limit);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("search(String, int, int) - end"); //$NON-NLS-1$
		}
		return list;
	}

	public int countAllSearch(String searchval) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("countAllSearch(String) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(
						SystemGlobals.getSql("PictureModel.countAllSearch"));
		String val = "%" + searchval + "%";
		p.setString(1, val);
		p.setString(2, val);
		p.setString(3, val);
		p.setString(4, val);
		ResultSet rs = p.executeQuery();
		rs.next();
		int ret = rs.getInt(1);
		rs.close();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("countAllSearch(String) - end"); //$NON-NLS-1$
		}
		return ret;
	}

	public void vote(int pid, int score) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("vote(int, int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(SystemGlobals.getSql("PictureModel.vote"));
		p.setInt(1, score);
		p.setInt(2, pid);
		p.executeUpdate();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("vote(int, int) - end"); //$NON-NLS-1$
		}
	}

	public List getTop(int limit) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("getTop(int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(
						SystemGlobals.getSql("PictureModel.selectTopRated"));
		p.setInt(1, limit);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("getTop(int) - end"); //$NON-NLS-1$
		}
		return list;
	}

	public void deleteByPID(int pid) throws Exception {
		if (logger.isDebugEnabled()) {
			logger.debug("deleteByPID(int) - start"); //$NON-NLS-1$
		}

		PreparedStatement p = JForumExecutionContext.getConnection()
				.prepareStatement(
						SystemGlobals.getSql("PictureModel.deleteByPID"));
		p.setInt(1, pid);
		p.executeUpdate();
		p.close();

		if (logger.isDebugEnabled()) {
			logger.debug("deleteByPID(int) - end"); //$NON-NLS-1$
		}
	}

}
